# -*- coding: utf-8 -*-
from __future__ import with_statement


__author__ = "Sérgio Severino, 9733"
__version__ = "0.1a from 2/12/2011"

import gc
from com.ziclix.python.sql import zxJDBC
import csv
import glob
import sys
from operator import itemgetter



class DB:
    '''
    A class to store some constants
    '''
    TEACHER_CODE = 0
    NAME = 1
    DEGREE = 2
    COURSE = 3
    CATEGORIE = 4
    WORK_TIME = 5
    YEAR = 6
    ESTABLISHMENT_TYPE = 7
    ESTABLISHMENT_NAME = 8
    pass

class Database:
    '''
    The class that manages all the sqlite and csv files work
    '''
    
    @staticmethod
    def csvFolderToSQliteDB( folder, sqLiteDB ):
        '''
        A static method that converts a set of csv files
        inside a folder to a sqlite database
        receives a folder name
        receives a sqlite filename
        '''

        #csv file header
        titles = []
        titlesAppend = titles.append
        
        connection = zxJDBC.connect("jdbc:sqlite:%s"  % sqLiteDB, '', '', "org.sqlite.JDBC")
        cursor = connection.cursor(1)
        cursorExec = cursor.execute
        cursorExecMany = cursor.executemany
        
        cursorExec('''drop table if exists tabela''')
        cursorExec('''create table if not exists tabela (
                            CodigodoDocente text,
                            Nome text,
                            UltimoGrau text,
                            Curso text,
                            Categoria text,
                            RegimeDeTempo text,
                            AnoLectivo text,
                            TipoDeEstabelecimento text,
                            NomeDoEstabelecimento text)''')

        csvRead = csv.reader
        
        # iterates over all csv files in the folder
        for filename in glob.glob( folder + "/*.csv" ):
            with open(filename, 'rb') as files:
                reader = csvRead(files, delimiter=',')
                nLine = 0
                for line in reader:
                    lineList = []
                    lineListAppend = lineList.append
                
                    if nLine == 0 and not titles:
                        for column in line:
                            lineListAppend(str(column))
                            pass
                        
                        titlesAppend(lineList) #csv header
                        pass
                    
                    elif nLine != 0:
                        stmt = "insert or replace into tabela values (?, ?, ?, ?, ?, ?, ?, ?, ?)"
                        cursorExecMany(stmt, [str(line[DB.TEACHER_CODE]).replace( '"', '☼' ).decode('utf-8'),# replace to prevent some errors
                                                  str(line[DB.NAME]).replace( '"', '☼' ).decode('utf-8'),
                                                  str(line[DB.DEGREE]).replace( '"', '☼' ).decode('utf-8'),
                                                  str(line[DB.COURSE]).replace( '"', '☼' ).decode('utf-8'),
                                                  str(line[DB.CATEGORIE]).replace( '"', '☼' ).decode('utf-8'),
                                                  str(line[DB.WORK_TIME]).replace( '"', '☼' ).decode('utf-8'),
                                                  str(line[DB.YEAR]).replace( '"', '☼' ).decode('utf-8'),
                                                  str(line[DB.ESTABLISHMENT_TYPE]).replace( '"', '☼' ).decode('utf-8'),
                                                  str(line[DB.ESTABLISHMENT_NAME]).replace( '"', '☼' ).decode('utf-8')] )
                                       
                        pass
                    
                    nLine = nLine + 1
                    pass
                files.close()
            pass
        connection.commit()
        cursor.close()
        connection.close()

        return titles
    
    def __init__( self, sqLiteDB ):
        '''
        This is the constructor
        it opens the sqlite database and copies it to a list
        '''

        # sqlite database file name
        self.__sqLiteDB = sqLiteDB
        
        connection = zxJDBC.connect("jdbc:sqlite:%s"  % self.__sqLiteDB, '', '', "org.sqlite.JDBC")
        cursor = connection.cursor(1)# dynamic cursor, due to memory issues in jython

        cursor.execute("select * from tabela")

        self.__Name = []
        self.__Years = []
        self.__EstablishmentName = []
        self.__Categories = []
        self.__Degree = []

        namesAppend = self.__Name.append
        yearsAppend = self.__Years.append
        establishmentAppend = self.__EstablishmentName.append
        categoriesAppend = self.__Categories.append
        degreeAppend = self.__Degree.append

        
        for line in cursor:
            lineList = []
            lineListAppend = lineList.append
            
            for column in line:
                # from unicode to utf-8, and undoes the previous replace
                lineList.append( column.encode('utf-8').replace( '☼', '"' ) )
                pass

            #temp lists
            namesAppend( lineList[DB.NAME])
            yearsAppend( lineList[DB.YEAR])
            establishmentAppend(lineList[DB.ESTABLISHMENT_NAME])
            categoriesAppend(lineList[DB.CATEGORIE])
            degreeAppend(lineList[DB.DEGREE])
            
            pass
        
        self.__differentYears = set(self.__Years)
        self.__differentEstablishmentName = set(self.__EstablishmentName)
        self.__differentCategories = set(self.__Categories)
        self.__differentDegree = set(self.__Degree)
        
        self.__differentYears = list( self.__differentYears )
        self.__differentYears.sort()
        
        self.__differentEstablishmentName = list( self.__differentEstablishmentName )
        self.__differentEstablishmentName.sort()
        
        self.__differentCategories = list( self.__differentCategories )
        self.__differentCategories.sort()
        
        self.__differentDegree = list( self.__differentDegree )
        self.__differentDegree.sort()
        
        cursor.close()
        connection.close()

        self.__updateData()
        pass

    def __updateData(self):
        '''
        A method that arranges some temp lists in the database list
        due to memory issues with jython
        the resulting list has missing columns
        '''
        # the database list
        self.__databaseList = []
        databaleListAppend = self.__databaseList.append

        for i in xrange(0, len(self.__Years)):
            databaleListAppend( [ '',
                                  self.__Name[i],
                                  self.__Degree[i],
                                  '',
                                  self.__Categories[i],
                                  '',
                                  self.__Years[i],
                                  '',
                                  self.__EstablishmentName[i] ] )
            pass

        # deletes the temporary lists
        del self.__Name
        del self.__Years
        del self.__EstablishmentName
        del self.__Categories
        del self.__Degree
        pass
    
    
    def getDifYears(self):
        '''
        Return method for the list __differentYears
        '''
        return self.__differentYears
    
    
    def getDifEstablishmentName(self):
        '''
        Return method for the list __differentEstablishmentName
        '''
        return self.__differentEstablishmentName
    

    def getDifCategories(self):
        '''
        Return method for the list __differentCategories
        '''
        return self.__differentCategories
    

    def getDifDegree(self):
        '''
        Return method for the list __differentDegree
        '''
        return self.__differentDegree
    

    def getNTheachersPerEstablishmentPerYear(self, establishmentName, year):
        '''
        This method searchs the database for the quantity of theachers
        in the given establishmentName and in the given year
        then returns their quantity
        '''
        return len(filter(lambda line: line[DB.YEAR] == year,
                          filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishmentName,
                                 self.getDBList())))


    def getNTheachersPerCategoriePerEstablishmentPerYear(self, categorie, establishmentName, year):   
        '''
        This method searchs the database for the quantity of theachers
        in the given establishmentName, year and categorie
        then returns their quantity
        ''' 
        return len(filter(lambda line: line[DB.CATEGORIE] == categorie,
                          filter(lambda line: line[DB.YEAR] == year,
                                 filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishmentName,
                                        self.getDBList()) )))

    def getNTheachersPerDegreePerEstablishmentPerYear(self, degree, establishmentName, year):
        '''
        This method searchs the database for the quantity of theachers
        in the given establishmentName, year and degree
        then returns their quantity
        '''
        return len(filter(lambda line: line[DB.DEGREE] == degree,
                          filter(lambda line: line[DB.YEAR] == year,
                                 filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishmentName,
                                        self.getDBList()))))
    

    def getDBList(self):
        '''
        returns a list with the database
        '''
        return self.__databaseList
    
    
    def getDBLenght(self):
        '''
        returns the database lenght
        '''
        return len(self.getDBList())


##    def createCsvFileOfNTheachersPerEstablishmentPerYear(self):
##        '''
##        This method creates a csv file with the number of teacher
##        per establishment per year listed in the database
##        '''
##        tmpList = []
##
##        getNTheachersPerEstablishmentPerYear = self.getNTheachersPerEstablishmentPerYear
##        append = tmpList.append
##        establishments = self.getDifEstablishmentName()
##        years = self.getDifYears()
##
##        for establishment in establishments:
##            for year in years:
##                quantity = getNTheachersPerEstablishmentPerYear(establishment, year)
##                append([establishment, year, str(quantity)])
##                pass
##            pass
##
##
##
##        csvFile = open('NTeachersPerEsPerYear.csv', 'wb')
##        
##        spamWriter = csv.writer( csvFile, delimiter=',' )
##        spamWriter.writerow(['Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'])
##        spamWriter.writerows(tmpList)
##
##        csvFile.close()
##        pass
##
##
##    def createCsvFileOfNTheachersPerCategoriePerEstablishmentPerYear(self):
##        '''
##        This method creates a csv file with the number of teacher
##        per categorie per establishment per year listed in the database
##        '''
##        tmpList = []
##        append = tmpList.append
##        establishments = self.getDifEstablishmentName()
##        years = self.getDifYears()
##        categories = self.getDifCategories()
##        getNTheachersPerCategoriePerEstablishmentPerYear = self.getNTheachersPerCategoriePerEstablishmentPerYear
##        
##        for categorie in categories:
##            for establishment in establishments:
##                for year in years:
##                    quantity = getNTheachersPerCategoriePerEstablishmentPerYear(categorie, establishment, year)
##                    append([categorie, establishment, year, str(quantity)])
##                pass
##            pass
##        
##        csvFile = open('NTeachersPerEsPerYear.csv', 'wb')
##        
##        spamWriter = csv.writer( csvFile, delimiter=',' )
##        spamWriter.writerow(['Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'])
##        spamWriter.writerows(tmpList)
##
##        csvFile.close()
##        pass
##    
##
##    def createCsvFileOfNTheachersPerDegreePerEstablishmentPerYear(self):
##        '''
##        This method creates a csv file with the number of teacher
##        per degree per establishment per year listed in the database
##        '''
##        tmpList = []
##        append = tmpList.append
##        establishments = self.getDifEstablishmentName()
##        years = self.getDifYears()
##        degrees = self.getDifDegree()
##        getNTheachersPerDegreePerEstablishmentPerYear = self.getNTheachersPerDegreePerEstablishmentPerYear
##
##        for degree in degrees:
##            for establishment in establishments:
##                for year in years:
##                    quantity = getNTheachersPerDegreePerEstablishmentPerYear(categorie, establishment, year)
##                    append([degree, establishment, year, str(quantity)])
##                    pass
##                pass
##            pass
##        
##        csvFile = open('NTeachersPerEsPerYear.csv', 'wb')
##        
##        spamWriter = csv.writer( csvFile, delimiter=',' )
##        spamWriter.writerow(['Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'])
##        spamWriter.writerows(tmpList)
##
##        csvFile.close()
##        pass



    def createCsvFile( self, filename, filelist ):
        '''
        This method creates a csv file 
        '''
        csvFile = open( filename, 'wb')
        
        spamWriter = csv.writer( csvFile, delimiter=',' )
        spamWriter.writerows(filelist)

        csvFile.close()
        pass

    def createCSVStatsPerEstablishment(self, establishment ):
        '''
        This method produces the statistics about
        the given argument
        '''

        tmpList = []

        getNTheachersPerEstablishmentPerYear = self.getNTheachersPerEstablishmentPerYear
        append = tmpList.append
        append( ['Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'] )
        years = self.getDifYears()

        for year in years:
            quantity = getNTheachersPerEstablishmentPerYear(establishment, year)
            append([establishment, year, str(quantity)])
            pass
        filename = '%s stats.csv' % establishment
        self.createCsvFile( filename, tmpList )

        pass

    def createCSVTListPerEstablishment( self, establishment ):
        '''
        This method produces a list with all teachers in
        the given argument
        '''

        tmpList = filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishment,
                                        self.getDBList())
        
        tmpList1 = []
        tmpList1Append = tmpList1.append

        for line in tmpList:
            tmpList1Append( [ line[DB.ESTABLISHMENT_NAME], line[DB.YEAR], line[DB.NAME] ] )
            pass
            
        tmpList1 = sorted( tmpList1, key=itemgetter(1, 2)) 
        tmpList1.insert( 0, ['Estabelecimento', 'Ano Lectivo', 'Nome do professor'] )

        filename = '%s teacher list.csv' % establishment
        self.createCsvFile( filename, tmpList1 )

        pass

    def createCSVStatsPerCategoriePerEstablishment(self, categorie, establishment ):
        '''
        This method produces the statistics about
        the given arguments
        '''
        tmpList = []

        getNTheachersPerCategoriePerEstablishmentPerYear = self.getNTheachersPerCategoriePerEstablishmentPerYear
        append = tmpList.append
        append( ['Categoria', 'Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'] )
        years = self.getDifYears()

        for year in years:
            quantity = getNTheachersPerCategoriePerEstablishmentPerYear(categorie, establishment, year)
            append([categorie, establishment, year, str(quantity)])
            pass
        filename = '%s in %s stats.csv' % (categorie, establishment)
        self.createCsvFile( filename, tmpList )

        pass

    def createCSVTListPerCategoriePerEstablishment( self, categorie, establishment ):
        '''
        This method produces a list with all teachers in
        the given arguments
        '''

        tmpList = filter(lambda line: line[DB.CATEGORIE] == categorie,
                                 filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishment,
                                        self.getDBList()))
        
        tmpList1 = []
        tmpList1Append = tmpList1.append

        for line in tmpList:
            tmpList1Append( [ line[DB.CATEGORIE], line[DB.ESTABLISHMENT_NAME], line[DB.YEAR], line[DB.NAME] ] )
            pass
            
        tmpList1 = sorted( tmpList1, key=itemgetter(2, 3)) 
        tmpList1.insert( 0, ['Categoria', 'Estabelecimento', 'Ano Lectivo', 'Nome do professor'] )

        filename = '%s in %s teacher list.csv' % (categorie, establishment)
        self.createCsvFile( filename, tmpList1 )

        pass

    def createCSVStatsPerDegreePerEstablishment(self, degree, establishment ):
        '''
        This method produces the statistics about
        the given arguments
        '''

        tmpList = []

        getNTheachersPerDegreePerEstablishmentPerYear = self.getNTheachersPerDegreePerEstablishmentPerYear
        append = tmpList.append
        append( ['Ultimo grau', 'Estabelecimento', 'Ano Lectivo', 'Quantidade de professores'] )
        years = self.getDifYears()

        for year in years:
            quantity = getNTheachersPerDegreePerEstablishmentPerYear(degree, establishment, year)
            append([degree, establishment, year, str(quantity)])
            pass
        filename = '%s in %s stats.csv' % (degree, establishment)
        self.createCsvFile( filename, tmpList )

        pass

    def createCSVTListPerDegreePerEstablishment( self, degree, establishment ):
        '''
        This method produces a list with all teachers in
        the given arguments
        '''

        tmpList = filter(lambda line: line[DB.DEGREE] == degree,
                                 filter(lambda line: line[DB.ESTABLISHMENT_NAME] == establishment,
                                        self.getDBList()))
        
        tmpList1 = []
        tmpList1Append = tmpList1.append

        for line in tmpList:
            tmpList1Append( [ line[DB.DEGREE], line[DB.ESTABLISHMENT_NAME], line[DB.YEAR], line[DB.NAME] ] )
            pass
            
        tmpList1 = sorted( tmpList1, key=itemgetter(2, 3)) 
        tmpList1.insert( 0, ['Ultimo grau', 'Estabelecimento', 'Ano Lectivo', 'Nome do professor'] )

        filename = '%s in %s teacher list.csv' % (degree, establishment)
        self.createCsvFile( filename, tmpList1 )

        pass
    

